import openpyxl
from mysql_writ.Mysql_write import Mysql_write


class Excel_write:
    def __init__(self,file_path):
        self.file_path = file_path
        self.wb = openpyxl.load_workbook(file_path)
        self.ws = self.wb.active



    # 拆分功能，太冗余了
    def _update_headers(self, headers):

        existing_headers = [                                         # 获取已存在的表头
            self.ws.cell(row=1, column=col_num).value if self.ws.cell(row=1, column=col_num).value is not None
            else ''                                                  # 判断列是否为空，若为空则设置为空字符串，若不为空则保留当前值
            for col_num in range(1, len(headers) + 1)
        ]

        if headers != existing_headers:                              # 如果表头不一致，则更新表头，否则不更新
            for col_num, header in enumerate(headers, 1):
                self.ws.cell(row=1, column=col_num, value=header)
            self.ws.save(self.file_path)
            print("打印成功，表头已更新")
        else:
            print("打印失败，表头已经存在")

    def write_to_excel(self,headers, data):
        # 调用私有方法更新表头
        self._update_headers(headers)

        for i in data:
            self.ws.append(i)
        self.wb.save(self.file_path)
        print("数据写入成功")


    def write_to_mysql(self):

        # 读取数据（跳过第一行）
        # 有bug，如果excel表里有东西，则数据会重复写入到数据库中（未解决）
        rows = list(self.ws.iter_rows(min_row=2, values_only=True))

        # 连接数据库
        mysql_writer = Mysql_write()
        conn = mysql_writer.connect()

        # 三引号可以使sql 多行输入，易读
        sql = """
           INSERT INTO user_info (
               id, idcard, username, realname, pwd, telphone, email, age, sex, address,
               hiredate, sal, job, company
           ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
           """

        # 向数据库写入数据
        try:
            with conn.cursor() as cursor:
                cursor.executemany(sql, rows)
                conn.commit()
                print(f"数据写入成功，打印了{len(rows)}数据")
        except Exception as e:
            conn.rollback()
            print(f"数据写入失败：{e}")
        finally:
            cursor.close()
            conn.close()











